{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aaceb216-d941-44a2-acef-cf11ea2df53f",
   "metadata": {},
   "source": [
    "# Feedback Analysis Example\n",
    "## Prerequisites\n",
    "Download conversation log using Athena. Following is an example query to get conversation with user's feedback:\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "    d.newimage.PK.S AS UserId,\n",
    "    d.newimage.SK.S AS ConvId,\n",
    "    d.newimage.TotalPrice.N AS TotalPrice,\n",
    "    d.newimage.MessageMap.S AS MessageMap\n",
    "FROM \n",
    "    bedrockchatstack_usage_analysis.ddb_export d\n",
    "WHERE \n",
    "    d.datehour BETWEEN '2024/04/01/00' AND '2024/04/29/23'\n",
    "    AND REGEXP_LIKE(d.newimage.MessageMap.S, '\"feedback\":((?!\\snull).)')\n",
    "ORDER BY\n",
    "    d.datehour DESC\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4897c330-b12d-4bfe-88c9-6c770d98649f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47bc6a80-bbdf-4ead-9693-d74aa3860b40",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from IPython import display\n",
    "\n",
    "def extract_conversation(data):\n",
    "    # Extract conversation to display.\n",
    "    conversation = []\n",
    "\n",
    "    for key, value in data.items():\n",
    "        if key not in (\"system\", \"instruction\"):\n",
    "            # print(f\"key: {key}\")\n",
    "            # print(f\"value: {value}\")\n",
    "            role = value['role']\n",
    "            feedback = value.get('feedback')\n",
    "            used_chunks = value.get('used_chunks')\n",
    "            content = value['content'][0]['body']\n",
    "\n",
    "            content = content.replace('\\\\n', '\\n')\n",
    "\n",
    "            conversation.append(f\"### {role.capitalize()}:\\n\\n{content.strip()}\")\n",
    "            if feedback:\n",
    "                category = feedback.get('category')\n",
    "                comment = feedback.get('comment')\n",
    "                conversation.append(f\"### feedback:\\n\\ncategory: {category}\\n\\ncomment: {comment}\")\n",
    "                \n",
    "            if used_chunks:\n",
    "                conversation.append(f\"### used_chunks:\\n\\n{used_chunks}\")\n",
    "\n",
    "    return \"\\n\\n\".join(conversation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25388648-cc07-47d5-a214-245320c97e1c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Enter downloaded csv file path from athena\n",
    "filepath = './xxx.csv'\n",
    "df = pd.read_csv(filepath)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a6fc548-3d1c-4b12-947a-1dd5c6468c75",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Display conversation with feedback and used RAG chunks\n",
    "display.Markdown(extract_conversation(json.loads(df[\"MessageMap\"].iloc[1])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65d7b127-091c-4afb-97c2-d4ae7552b3a6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f9d7cbb-746a-4ce6-a398-7eb292e988cf",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
